package com.maixin.code.utils;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.maixin.code.entity.UserWorkTimeInfo;

/**
 * 提供操作Excel的工具类
 * 
 * @author SJ
 * 
 */
public class ExcelUtils {
	
	
	/**
	 * 根据用户上传的Excel返回workBook
	 * @param file
	 * @return
	 */
	public static Workbook loadExcel(File file){
			Workbook workBook = null;
			try {
				workBook = new XSSFWorkbook(new FileInputStream(file)); // 支持2007
			} catch (Exception ex) {
				try {
					workBook = new HSSFWorkbook(new FileInputStream(file)); // 支持2003及以前
				} catch (Exception e) {
					e.printStackTrace();
					workBook = null;
				}
			}
		return workBook;
	}


	public static void main(String args[]) throws Exception {

		ExcelUtils im = new ExcelUtils();
		//im.loadExcelWorkTime("f:/2.xls");
//		 im.readExcel("f:/1.xlsx");
		// im.readExcel("F:/2007.xlsx");
		// im.readExcel("F:/2007.xls"); //2007下保存为2003

	}

	
	public static List<List<UserWorkTimeInfo>> loadExcelWorkTime(File file) throws Exception{

		Workbook workBook = loadExcel(file);
		// 循环每个工作表
		for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
			// 创建工作表
			Sheet sheet = workBook.getSheetAt(i);
			int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
			if (rows > 0) {
				sheet.getMargin(Sheet.TopMargin);
				List<List<UserWorkTimeInfo>> list = new ArrayList<List<UserWorkTimeInfo>>();
				for (int r = 1; r < rows; r++) { // 从第二行数据开始循环,不要表格的表头信息
					Row row = sheet.getRow(r);
					if (row != null) {
						String singNum = row.getCell(2).toString();
						List<UserWorkTimeInfo> workList = new ArrayList<UserWorkTimeInfo>();
						for (int j = 1; j < rows; j++) { // 行循环
							Row s =sheet.getRow(j);
							//根据上传的Excel的登记号码来判断
							if(singNum == s.getCell(2).toString()){
								UserWorkTimeInfo workTime = new UserWorkTimeInfo();
								workTime.setDeptName(getValue(s.getCell(0)));
								workTime.setUsername(getValue(s.getCell(1)));
								workTime.setSingNum(getValue(s.getCell(2)));
								workTime.setWorkTimePm(getValue(s.getCell(3)));
								workTime.setState(getValue(s.getCell(4)));
								workTime.setMachineNum(getValue(s.getCell(5)));
								workTime.setWorkCode(getValue(s.getCell(7)));
								workTime.setComparison(getValue(s.getCell(8)));
								workTime.setUserId(getValue(s.getCell(6)));
								workList.add(workTime);
								r= j;
							}
						}
						list.add(workList);
					}
				}
				return list;
			}
		}
		return null;
	}
	
	public void readExcel(String filePath) {

		try {

			Workbook workBook = null;
			try {
				workBook = new XSSFWorkbook(filePath); // 支持2007
			} catch (Exception ex) {
				workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
			}

			// 获得Excel中工作表个数
			System.out.println("工作表个数 :" + workBook.getNumberOfSheets());

			// 循环每个工作表
			for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
				// 创建工作表
				Sheet sheet = workBook.getSheetAt(i);

				int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
				if (rows > 0) {
					sheet.getMargin(Sheet.TopMargin);
					for (int r = 0; r < rows; r++) { // 行循环
						Row row = sheet.getRow(r);
						if (row != null) {

							int cells = row.getLastCellNum();// 获得列数
							for (short c = 0; c < cells; c++) { // 列循环
								Cell cell = row.getCell(c);

								if (cell != null) {
									String value = getValue(cell);
									System.out.println("第" + r + "行 " + "第" + c
											+ "列：" + value);
								}
							}
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static String getValue(Cell cell) {
		String value = "";
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC: // 数值型
			if (DateUtil.isCellDateFormatted(cell)) {
				// 如果是date类型则 ，获取该cell的date值
				value = DateUtil.getJavaDate(cell.getNumericCellValue())
						.toString();
			} else {// 纯数字
				value = String.valueOf(cell.getNumericCellValue());
			}
			break;
		/* 此行表示单元格的内容为string类型 */
		case Cell.CELL_TYPE_STRING: // 字符串型
			value = cell.getRichStringCellValue().toString();
			break;
		case Cell.CELL_TYPE_FORMULA:// 公式型
			// 读公式计算值
			value = String.valueOf(cell.getNumericCellValue());
			if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
				value = cell.getRichStringCellValue().toString();
			}
			// cell.getCellFormula();读公式
			break;
		case Cell.CELL_TYPE_BOOLEAN:// 布尔
			value = " " + cell.getBooleanCellValue();
			break;
		/* 此行表示该单元格值为空 */
		case Cell.CELL_TYPE_BLANK: // 空值
			value = "";
			break;
		case Cell.CELL_TYPE_ERROR: // 故障
			value = "";
			break;
		default:
			value = cell.getRichStringCellValue().toString();
		}
		return value;
	}

}
